For traditional small to medium-sized data, tools like Excel, formulas, and VBA are used. The scope of processing includes data import/export, data cleaning, data preprocessing, statistical analysis, and data visualization.
Data Cleaning refers to file-level data processing, including column operations, row operations, value operations, data queries, sorting, ranking, filtering, data splitting/merging, text data cleaning, and date/time data cleaning.
Data Preprocessing addresses problematic data in files that would otherwise affect the analysis process or results. It covers duplicate data handling, missing value treatment, outlier detection, and data transformation. Once data is prepared, statistical analysis and visualization can be performed.
Processing Data via Excel GUI
As an excellent office software, Excel provides a user-friendly GUI that simulates real-world office scenarios, delivering a familiar experience. With the GUI, users can easily complete tasks using a mouse and keyboard.
It is important to note that behind the GUI lies code. The apparent ease of use masks the fact that programs are running in the background. The GUI encapsulates common functions into buttons, menus, and other visual elements, with event responses triggering instructions. Input/output is presented as charts/tables, enabling "what you see is what you get" (WYSIWYG).
Most Excel users rely on this method for daily tasks, which suffices for most office work.
Processing Data via Formulas
While the GUI offers many built-in functions, it often falls short for custom data processing needs not covered by its features. Thus, Excel provides formulas and VBA for programming custom logic.
A formula is essentially an expression calculator: it starts with an equals sign (=) followed by operators and values. Operators include arithmetic (e.g., +, -), relational (e.g., >, =), and logical (e.g., AND, OR) operators. Values encompass numbers, strings, dates/times, cell/range references, and function returns.
Formulas follow strict syntax rules. Despite being a single line of code, they include operators, operands, conditional structures, loops, and functions—core elements of high-level programming languages. As the saying goes, "small but complete."
Formulas are suitable for lightweight programming tasks. If formulas become overly complex or excessive, VBA programming should be considered.
Processing Data via Excel VBA
Excel VBA processes data through programming.
Section 1.1.1 explained that the Excel GUI hides programming in the backend, enabling mouse/keyboard-only interaction. Why abandon the GUI for programming?
In short: programming solves tasks the GUI cannot handle. These tasks include:
Automating repetitive workflows: e.g., generating reports after each data analysis, creating payslips for 1,000 employees, drafting 100 contracts, or renaming 2,000 files. Programming drastically boosts efficiency and reduces errors.
Extending Excel’s functionality: programming enables features not built into Excel.
Excel VBA is an advanced skill for data processing. Mastering it requires effort but pays off by streamlining tasks and freeing up time.